"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : security-audit
Case Name   : update returning审计
Create At   : 2023/06/08
Owner       : opentestcase033
Description :
    1.开启DML操作审计功能
    2.创建测试表并插入数据
    3.执行delete returning语句
    4.查询审计记录
    5.清理环境
Expect      :
    1.设置成功
    2.成功
    3.成功
    4.审计到delete returning记录
    5.成功
History     :
"""

import os
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class Audit(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.cons = Constant()
        self.table = 't_audit_0003'

    def test_security(self):
        text = '-----step1:开启DML操作审计功能;expect:成功-----'
        self.log.info(text)
        res = self.pri_sh.execute_gsguc('reload', self.cons.GSGUC_SUCCESS_MSG,
                                        'audit_dml_state=1')
        self.assertTrue(res, f"执行失败{text}")

        text = '-----step2:创建测试表并插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.table} cascade;
        create table {self.table}(
        id int, role varchar(100), data varchar(100));
        insert into {self.table} values
        (1, 'alice', 'alice data'), (2, 'bob', 'bob data');'''
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.cons.TABLE_CREATE_SUCCESS, msg, f"执行失败:{text}")
        self.assertIn(self.cons.INSERT_SUCCESS_MSG, msg, f"执行失败:{text}")

        text = '-----step3:执行update returning语句;expect:成功-----'
        self.log.info(text)
        delete_sql = f"delete from {self.table} where id = 1 returning *;"
        msg = self.pri_sh.execut_db_sql(delete_sql)
        self.log.info(msg)
        self.assertIn(self.cons.DELETE_SUCCESS_MSG, msg, f"执行失败:{text}")

        text = '-----step4:查询审计记录;expect:审计到update returning记录-----'
        self.log.info(text)
        sql = f"select pg_sleep(2);" \
              f"select type, object_name, detail_info from " \
              f"pg_query_audit(sysdate - interval '20 seconds', sysdate) " \
              f"where type='dml_action' and object_name='{self.table}';"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(delete_sql, msg, f"执行失败:{text}")

    def tearDown(self):
        text = '-----step5:清理环境;expect:成功-----'
        self.log.info(text)
        msg = self.pri_sh.execut_db_sql(
            f'drop table if exists {self.table} cascade;')
        self.log.info(msg)
        res = self.pri_sh.execute_gsguc('reload', self.cons.GSGUC_SUCCESS_MSG,
                                        'audit_dml_state=0')
        self.assertTrue(res, f"执行失败:{text}")
        self.assertIn(self.cons.TABLE_DROP_SUCCESS, msg, f"执行失败:{text}")
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
